﻿Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports System.Data.SqlTypes

Public Class Connection
    Public Shared sConn As SqlConnection
    Public Shared sConnSAP As SqlConnection
    Public Shared bConnect As Boolean
    Public Sub setDB()
        Try
            Dim strConnect As String = ""
            Dim sCon As String = ""
            Dim SQLType As String = ""
            Dim sErrMsg As String = ""
            strConnect = "SAPConnect"
            Dim sPath As String
            sPath = IO.Directory.GetParent(Application.StartupPath).ToString.Trim()
            Dim objIniFile As New INIClass(sPath.Replace("bin", "") & "\" & "ConfigFile.ini")
            PublicVariable.SQLDB = objIniFile.GetString("SQL", "SQLDB", "")
            PublicVariable.SAPUser = objIniFile.GetString("SAP", "SAPUser", "")
            PublicVariable.SAPPwd = objIniFile.GetString("SAP", "SAPPwd", "")
            PublicVariable.SQLServer = objIniFile.GetString("SQL", "SQLServer", "")
            PublicVariable.SQLUser = objIniFile.GetString("SQL", "SQLUser", "")
            PublicVariable.SQLPwd = objIniFile.GetString("SQL", "SQLPwd", "")
            PublicVariable.SAPLicenseServer = objIniFile.GetString("SAP", "SAPLicenseServer", "")
            PublicVariable.SQLType = objIniFile.GetString("SQL", "SQLType", "")
            PublicVariable.CountryCode = objIniFile.GetString("SAP", "CountryCode", "")
            PublicVariable.Series = objIniFile.GetString("SAP", "ItemSeries", "")
            If IsNothing(PublicVariable.oCompany) Then
                PublicVariable.oCompany = New SAPbobsCOM.Company
            End If
            PublicVariable.oCompany.CompanyDB = PublicVariable.SQLDB ' MyArr(0).ToString.Trim()
            PublicVariable.oCompany.UserName = PublicVariable.SAPUser ' MyArr(1).ToString.trim()
            PublicVariable.oCompany.Password = PublicVariable.SAPPwd ' MyArr(2).ToString.trim()
            PublicVariable.oCompany.Server = PublicVariable.SQLServer ' MyArr(3).ToString.trim()
            PublicVariable.oCompany.DbUserName = PublicVariable.SQLUser ' MyArr(4).ToString.trim()
            PublicVariable.oCompany.DbPassword = PublicVariable.SQLPwd ' MyArr(5).ToString.Trim()
            PublicVariable.oCompany.LicenseServer = PublicVariable.SAPLicenseServer ' MyArr(6)
            SQLType = PublicVariable.SQLType ' MyArr(7)
            If SQLType = 2008 Then
                PublicVariable.oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2008
            ElseIf SQLType = 2005 Then
                PublicVariable.oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2005
            Else
                PublicVariable.oCompany.DbServerType = SAPbobsCOM.BoDataServerTypes.dst_MSSQL2012
            End If

        Catch ex As Exception
            Functions.WriteLog(ex.ToString)
        End Try
    End Sub
    Public Function connectDB() As Boolean
        Try
            Dim sErrMsg As String = ""
            Dim connectOk As Integer = 0
            If PublicVariable.oCompany.Connect() <> 0 Then
                PublicVariable.oCompany.GetLastError(connectOk, sErrMsg)
                bConnect = False
                Functions.WriteLog("Error Msg:" & sErrMsg)
                Return False
            Else
                bConnect = True
                Return True
            End If
        Catch ex As Exception

            Return False
            Functions.WriteLog(ex.ToString)
        End Try
    End Function
    '#Region "ADO SAP"
    '    Private Function GetConnectionString_SAP() As SqlConnection
    '        If sConnSAP.State = ConnectionState.Open Then
    '            sConnSAP.Close()
    '        End If
    '        Try
    '            sConnSAP.Open()
    '        Catch ex As Exception
    '            Functions.WriteLog(ex.ToString)
    '        End Try
    '        Return sConnSAP
    '    End Function
    '    Public Function ObjectGetAll_Query_SAP(ByVal QueryString As String) As DataSet
    '        Try

    '            Using myConn = GetConnectionString_SAP()
    '                Dim MyCommand As SqlCommand = New SqlCommand(QueryString, myConn)
    '                MyCommand.CommandType = CommandType.Text
    '                Dim da As SqlDataAdapter = New SqlDataAdapter()
    '                Dim mytable As DataSet = New DataSet()
    '                da.SelectCommand = MyCommand
    '                da.Fill(mytable)
    '                myConn.Close()
    '                Return mytable
    '            End Using
    '        Catch ex As Exception
    '            Functions.WriteLog(ex.ToString)
    '            Return Nothing
    '        End Try
    '    End Function
    '    Public Function Integration_RunQuery(ByVal querystr As String) As DataTable
    '        If querystr = "" Then Return Nothing

    '        Try
    '            Dim MyArr As Array
    '            Dim Str As String
    '            Str = System.Configuration.ConfigurationSettings.AppSettings.Get("IntegrationConnectionString")
    '            MyArr = Str.Split(";")

    '            Dim NConnection = New SqlConnection
    '            NConnection.ConnectionString = "server= " + MyArr(1).ToString() + ";database=" + MyArr(0).ToString() + " ;uid=" + MyArr(2).ToString() + "; pwd=" + MyArr(3).ToString() + ";"
    '            NConnection.Open()

    '            Dim MyCommand As SqlCommand = New SqlCommand(querystr, NConnection)
    '            MyCommand.CommandType = CommandType.Text
    '            MyCommand.CommandTimeout = 0

    '            Dim da As SqlDataAdapter = New SqlDataAdapter()
    '            Dim mytable As DataTable = New DataTable()
    '            da.SelectCommand = MyCommand
    '            da.Fill(mytable)
    '            NConnection.Close()
    '            If mytable Is Nothing Then Return Nothing
    '            Return mytable
    '        Catch ex As Exception
    '            Functions.WriteLog("Integration_RunQuery: " + querystr + " ERROR:" + ex.Message)
    '            Return Nothing
    '        End Try
    '    End Function

    '#End Region

#Region "ADO Integration"
    Private Function Integration_OpenSQLConnection() As Boolean
        PublicVariable.IntegrationConnection = New SqlConnection
        PublicVariable.IntegrationConnection.ConnectionString = PublicVariable.IntegrationConnectionString

        If PublicVariable.IntegrationConnection.State = ConnectionState.Open Then
            PublicVariable.IntegrationConnection.Close()
        End If
        Try
            PublicVariable.IntegrationConnection.Open()
        Catch ex As Exception
            Functions.WriteLog("Integration_OpenSQLConnection:" + ex.Message)
            Return False
        End Try
        Return True
    End Function
    Public Function Integration_RunQuery(ByVal querystr As String) As DataTable
        If querystr = "" Then Return Nothing

        Try
            Dim MyArr As Array
            Dim Str As String
            Str = System.Configuration.ConfigurationSettings.AppSettings.Get("IntegrationConnectionString")
            MyArr = Str.Split(";")

            Dim NConnection = New SqlConnection
            NConnection.ConnectionString = "server= " + MyArr(1).ToString() + ";database=" + MyArr(0).ToString() + " ;uid=" + MyArr(2).ToString() + "; pwd=" + MyArr(3).ToString() + ";"
            NConnection.Open()

            Dim MyCommand As SqlCommand = New SqlCommand(querystr, NConnection)
            MyCommand.CommandType = CommandType.Text
            MyCommand.CommandTimeout = 0

            Dim da As SqlDataAdapter = New SqlDataAdapter()
            Dim mytable As DataTable = New DataTable()
            da.SelectCommand = MyCommand
            da.Fill(mytable)
            NConnection.Close()
            If mytable Is Nothing Then Return Nothing
            Return mytable
        Catch ex As Exception
            Functions.WriteLog("Integration_RunQuery: " + querystr + " ERROR:" + ex.Message)
            Return Nothing
        End Try
    End Function
#End Region
#Region "ADO SAP"
    Private Function SAP_OpenSQLConnection() As Boolean
        PublicVariable.SAPConnection = New SqlConnection
        Dim MyArr As Array
        Dim Str As String
        Str = System.Configuration.ConfigurationSettings.AppSettings.Get("SAPConnectionString")
        MyArr = Str.Split(";")
        Dim constr As String = "server= " + MyArr(3).ToString() + ";database=" + MyArr(0).ToString() + " ;uid=" + MyArr(4).ToString() + "; pwd=" + MyArr(5).ToString() + ";"

        PublicVariable.SAPConnection.ConnectionString = constr 'PublicVariable.SAPConnectionString

        If PublicVariable.SAPConnection.State = ConnectionState.Open Then
            PublicVariable.SAPConnection.Close()
        End If
        Try
            PublicVariable.SAPConnection.Open()
        Catch ex As Exception
            Functions.WriteLog("SAP_OpenSQLConnection:" + ex.Message)
            Return False
        End Try
        Return True
    End Function
    Public Function SAP_RunQuery(ByVal querystr As String) As DataTable
        Try
            If SAP_OpenSQLConnection() Then
                Dim MyCommand As SqlCommand = New SqlCommand(querystr, PublicVariable.SAPConnection)
                MyCommand.CommandType = CommandType.Text
                MyCommand.CommandText = querystr
                Dim da As SqlDataAdapter = New SqlDataAdapter()
                Dim mytable As DataTable = New DataTable()
                da.SelectCommand = MyCommand
                da.Fill(mytable)
                PublicVariable.SAPConnection.Close()
                If mytable Is Nothing Then Return Nothing
                Return mytable
            Else
                Return Nothing
            End If
        Catch ex As Exception
            Functions.WriteLog("SAP_RunQuery:" + ex.Message)
            Return Nothing
        End Try
    End Function
#End Region
End Class
